/*
 * @Author: zs.duan
 * @Date: 2023-04-05 15:11:14
 * @LastEditors: zs.duan
 * @LastEditTime: 2023-05-01 19:07:05
 * @FilePath: \serve\utils\Create_SQL.js
 */

/**
 * 创建sql
 * */ 
function createSql(){
    
};
createSql.prototype = {
    sql : "",
    /**
     * 查询
     * @param {string | Array} [query] 查询的语句 默认 * 选填
     * @param {boolean} [isDistinct] 是否去重 选填 默认false
     * @example createSql.select("query").from("table_name").end()
     * @example createSql.select([query1 , query2]).from("table_name").end()
    */
    select:function(query = '*' , isDistinct = false ){
        if(typeof query == 'string'){
            this.sql = `SELECT ${query} `;
            if(isDistinct) this.sql = ` SELECT DISTINCT  ${query} `;
            return this
        }
        if(Array.isArray(query)){
            this.sql = `SELECT ${query.join(" , ")} `;
            if(isDistinct) this.sql = ` SELECT DISTINCT  ${query.join(",")} `;
            return this;
        }
        
    },
    /**
     * from 表名
     * @param {string | Array} table_name 表名称 | 表名称数组
     * @example createSql.select().from("table_name").end()
     * @example createSql.select().from([table_name1 , table_name2]).end()
     * */ 
    from : function(table_name){
        if(!table_name.trim()){
            return this;
        }
        if(typeof table_name == 'string'){
            this.sql += ` FROM ${table_name} `;
        }
        if(Array.isArray(table_name)){
            this.sql += ` FROM ${table_name.join(",")} `;
        }
        return this;
    },
    /**
     * 左连接
     * @param {string} left_jion_table_name 左连接表名
     * @example createSql.select().from("table_name").leftJoin("left_jion_table_name").end()
     * */ 
    leftJoin:function(left_jion_table_name){
        if(!left_jion_table_name.trim()){
            return this;
        }
        this.sql += ` LEFT JOIN ${left_jion_table_name} `;
        return this;
    },
    /**
     * join 连接
     * @param {string} join_table_name 连接表名
     * @example createSql.select().from("table_name").join("join_table_name").end()
     * */ 
    join: function(join_table_name) {
        if(!join_table_name.trim()){
            return this;
        }
        this.sql += ` INNER JOIN ${join_table_name} `;
        return this;
    },
    /**
     * 交叉连接
     * @param {string} cross_join_condition 交叉连接语句
     * @example createSql.select().from("table_name").where("where_condition").crossJoin("cross_join_condition").end()
     * */ 
    crossJoin: function(cross_join_condition) {
        if(!cross_join_condition.trim()){
            return this;
        }
        this.sql += ` CROSS JOIN ${cross_join_condition} `;
        return this;
    },
    /**
     * on 
     * @param {string} on_condition on处理语句
     * @example createSql.select().from("table_name").on("on_condition").build()
     * 
     * */ 
    on: function(on_condition , on_condition_two , on_condition_three) {
        if(!on_condition.trim()){
            return this;
        }
        if(Array.isArray(on_condition)){
            this.sql += ` ON ${on_condition.join(" AND ")} `
            return this;
        }
        if(on_condition_two){
            this.sql += ` ON ${on_condition} AND ${on_condition_two} `
            return this;
        }
        if(on_condition_three){
            this.sql += ` ON ${on_condition}  ${on_condition_two}  ${on_condition_three} `
            return this;
        }
        this.sql += ` ON ${on_condition} `
        return this;
    },
    /**
     * order 通过函数排序
     * @param {string} order_condition 排序语句
     * @param {string} order_condition_two 排序语句 2 选填 如果有第二个参数 则第一个参数为开始位置
     * @example createSql.select().from("table_name").order("order_condition").end()
     * */ 
    order: function(order_condition , order_condition_two) {
        if(!order_condition.trim()){
            return this;
        }
        this.sql += order_condition_two ? ` ORDER BY ${order_condition} ${order_condition_two} ` : ` ORDER BY ${order_condition} `;
        return this;
    },
    /**
     * limit 限制条数等
     * @param {string} limit_condition 限制语句
     * @param {string} limit_condition_two 限制语句 2 选填 如果有第二个参数 则第一个参数为开始位置
     * @example createSql.select().from("table_name").limit(1 , 10).end()
     * */ 
    limit: function(limit_condition , limit_condition_two) {
        limit_condition = limit_condition.toString();
        if(!limit_condition.trim()){
            return this;
        }
        let limit_condition_reslut = limit_condition.split(",");
        if(limit_condition_reslut.length > 1){
            this.sql += ` LIMIT ${limit_condition} `;
            return this;
        }
        this.sql += limit_condition_two ? ` LIMIT ${limit_condition} , ${limit_condition_two} ` : ` LIMIT 0 , ${limit_condition} `;
        return this;
    },
    /**
     * offset 去掉几行元素
     * @param {string} offset_condition 处理语句
     * @param {string} offset_condition_two 处理语句 2 选填 如果有第二个参数 则第一个参数为开始位置
     * @example createSql.select().from("table_name").offset(1).end()
     * */ 
    offset: function(offset_condition , offset_condition_two) {
        if(!offset_condition.trim()){
            return this;
        }
        this.sql += offset_condition_two ? ` OFFSET ${offset_condition} , ${offset_condition_two} ` : ` OFFSET ${offset_condition} `;
        return this;
    },
    /**
     * group 分组
     * @param {string} groupBy 分组语句
     * @example createSql.select().from("table_name").group("groupBy").end()
     * */ 
    group: function(groupBy) {
        if(!groupBy.trim()){
            return this;
        }
        this.sql += ` GROUP BY ${groupBy} `;
        return this;
    },
    /**
     * update 更新
     * @param {string} update_table_name 更新表名
     * @example createSql.update("table_name").set({key : value}).end()
     * @example createSql.update("table_name").set("set_condition").end()
     * */ 
    update: function(update_table_name) {
        this.sql = ` UPDATE ${update_table_name} `;
        return this;
    },
    /**
     * set 更新字段
     * @param {string | object} set_condition 更新语句/更新的对象
     * @example createSql.update("table_name").set({key : value}).end()
     * @example createSql.update("table_name").set("set_condition").end()
     * */ 
    set : function(set_condition){
        if(typeof set_condition == 'string' && !set_condition.trim()){
            return this;
        }
        if(typeof set_condition == 'string'){
            this.sql += `SET ${set_condition} `;
            return this;
        }
        this.sql += `SET ${setJsonSQL(set_condition , 'update').substring(0, setJsonSQL(set_condition , 'update').length - 2)} `;
        return this;
    },
    /**
     * 插入
     * @param {string} insert_table_name 插入表名
     * @example createSql.insert("table_name").value({key : value}).end()
     * @example createSql.insert("table_name").value("set_condition").end()
    */
    insert: function(insert_table_name) {
        this.sql = `INSERT INTO  ${insert_table_name} `;
        return this;
    },
    /**
     * value 插入数据
     * @param {string | object} value_condition 插入语句/更新的对象
     * @example createSql.insert("table_name").value({key : value}).end()
     * @example createSql.insert("table_name").value("set_condition").end()
     * */ 
    value : function(value_condition){
        if(typeof value_condition == 'string' && !value_condition.trim()){
            return this;
        }
        if(typeof value_condition == 'string'){
            this.sql += `SET ${value_condition} `;
            return this;
        }
        let firstSql = setJsonSQL(value_condition , 'insert').firstSql;
        let lastSql = setJsonSQL(value_condition , 'insert').lastSql;
        this.sql += `(${firstSql.slice(0, firstSql.length - 1)}) VALUE (${lastSql.slice(0, lastSql.length - 2)})`;;
        return this;
    },
    /**
     * values 插入多条数据
     * @param {Array} value_condition
     * @example createSql.insert("table_name").value([{key : value}]).builf()
     * */ 
    values(value_condition) {
        if(!Array.isArray(value_condition)) {
            throw new Error('values参数必须为数组');
        }
        let firstSql = setJsonSQL(value_condition[0] , 'insert').firstSql;
        let lastSqlList = [];
        value_condition.forEach(item => {
            lastSqlList.push(setJsonSQL(item , 'insert').lastSql);
        })
        let values = '';
        lastSqlList.forEach(item => {
            values += `(${item.slice(0, item.length - 2)}),`
        })
        this.sql += `(${firstSql.slice(0, firstSql.length - 1)}) VALUES ${values.slice(0, values.length - 1)};`;
        return this;
    },
    /**
     * 删除
     * @param {string} delete_table_name 删除表名
     * @example createSql.delete("delete_table_name").end()
    */
    delete: function(delete_table_name) {
        this.sql = ` DELETE FROM ${delete_table_name} `;
        return this;
    },
    /**
     * in 多条件 可用于 删除/查询等
     * @param {string} in_condition 多条件语句
     * @example createSql.delete("delete_table_name").in("in_condition").end()
    */
    in: function(in_condition) {
        if(!in_condition.trim()){
            return this;
        }
        this.sql += ` IN ${in_condition} `;
        return this;
    },
    /**
     * 条件
     * @param {string} where_condition 条件语句 / 参数
     * @param {string} [where_condition_two] 条件语句 2 选填 如果有第二个参数 则为第一个参数 = 第二个参数
     * @param {string} [where_condition_three] 条件语句 3 选填 如果有第三个参数 则第一个参数为开始位置 第二个参数为操作符 如 = like > < 等 ， 第三个参数为结束位置
     * @example createSql.select().from("table_name").where("where_condition").end()
     * @example createSql.select().from("table_name").where("where_condition" , "where_condition_two").end()
     * @example createSql.select().from("table_name").where("where_condition" , "like" , "where_condition_three").end()
     * @example createSql.delete("delete_table_name").where("where_condition").end()
    */
    where: function(where_condition , where_condition_two , where_condition_three) {
        if(!where_condition.trim()){
            return this;
        }
        if(!where_condition){
            return this;
        }
        if(where_condition_three){
            this.sql += ` WHERE ${where_condition} ${where_condition_two} ${where_condition_three}  `;
            return this;
        }
        if(where_condition_two){
            this.sql += ` WHERE ${where_condition} = ${where_condition_two} `;
            return this;
        }
        this.sql += ` WHERE ${where_condition} `;
        return this;
    },
    /**
     * andwhere
     * @param {string} and_condition and语句
     * @example createSql.select().from("table_name").where("where_condition").and("and_condition").end()
     * @example createSql.delete("delete_table_name").where("where_condition").and("and_condition").end()
     * */
    andwhere: function(and_condition) {
        if(!and_condition.trim()){
            return this;
        }
        if(this.sql.indexOf("WHERE") == -1){
            this.sql += ` WHERE ${and_condition} `;
            return this;
        }
        this.sql += ` AND ${and_condition} `;
        return this;
    },

    /**
     * and
     * @param {string} and_condition and语句
     * @example createSql.select().from("table_name").where("where_condition").and("and_condition").end()
     * @example createSql.delete("delete_table_name").where("where_condition").and("and_condition").end()
     * */
    and: function(and_condition) {
        if(!and_condition.trim()){
            return this;
        }
        if(this.sql.indexOf("WHERE") == -1){
            this.sql += ` WHERE ${and_condition} `;
            return this;
        }
        this.sql += ` AND ${and_condition} `;
        return this;
    },
    /**
     * between 语句
     * @param {string} between_condition between语句
     * @example createSql.select().from("table_name").where("where_condition").between("between_condition").and("and_condition").end()
     * */ 
    between: function(between_condition) {
        if(!between_condition.trim()){
            return this;
        }
        this.sql += ` BETWEEN ${between_condition} `;
        return this;
    },
    /**
     * is 语句
     * @param {string} is_condition is语句 一般用与 is [not]null
     * */ 
    is: function(is_condition) {
        if(!is_condition.trim()){
            return this;
        }
        this.sql += ` IS ${is_condition} `;
        return this;
    },
    /**
     * having 语句 过滤分组
     * @example createSql.select().from("table_name").where("where_condition").having("having_condition").end()
     * */ 
    having: function(having_condition) {
        if(!having_condition.trim()){
            return this;
        }
        this.sql +=  `HAVING ${having_condition} `;
        return this;
    },
    /**
     * 正则表达式
     * @param {string} regexp_condition like语句
     * @example createSql.select().from("table_name").where("where_condition").regexp("regexp_condition").end()
     * */ 
    regexp: function(regexp_condition) {
        if(!regexp_condition.trim()){
            return this;
        }
        this.sql += ` REGEXP ${regexp_condition} `;
        return this;
    },
    /**
     * or 
     * @param {string} or_condition or语句
     * @example createSql.select().from("table_name").where("where_condition").or("or_condition").end()
     * @example createSql.delete("delete_table_name").where("where_condition").or("or_condition").end()
     * */ 
    or: function(or_condition) {
        if(!or_condition.trim()){
            return this;
        }
        this.sql += `OR ${or_condition} `;
        return this;
    },
    /**
     * 语句结尾
     * @example createSql.select().from("table_name").where("where_condition").end()
     * @example createSql.delete("delete_table_name").where("where_condition").end()
     * @example createSql.update("table_name").set({key : value}).end()
     * @example createSql.insert("table_name").value({key : value}).end()
     * */ 
    end: function() {
        return this.sql;
    },
    /**
     * 语句结尾
     * @example createSql.select().from("table_name").where("where_condition").build()
     * @example createSql.delete("delete_table_name").where("where_condition").build()
     * @example createSql.update("table_name").set({key : value}).build()
     * @example createSql.insert("table_name").value({key : value}).build()
     * */ 
    build : function(){
        return this.sql;
    },
    /**
     * 自定义 语句 跟在后面
     * @param {string} custom_condition 自定义语句
     * @example createSql.custom("custom_condition").build()
     * */ 
    custom : function (custom_condition){
        this.sql += custom_condition;
        return this;
    },
    /**
     * 自定义语句 全自定义
     * @param {string} custom_condition 自定义语句
     * @example createSql.customAll("custom_condition").build()
     * */ 
    customAll : function (custom_condition){
        this.sql = custom_condition;
        return this;
    }
}

/**
 * 处理传过来的sql语句
 * @param {object} data 需要处理的对象函数
 * @param {'insert' | 'update'} type 处理类型
 * @return {string} 处理好的sql
 * */ 
const setJsonSQL = (data , type) =>{
    let reslut = {
        firstSql : "",
        lastSql : ""
    };
    for (const key in data) {
        if (Object.hasOwnProperty.call(data, key)) {
            const element = data[key];
            if(type == 'insert') reslut.firstSql += ` ${key} ,`;
            if(element !== 'false' && element !== 'true' && element !== false && element !== true){
                if(type == 'update') reslut.firstSql += ` ${key} = '${data[key]}' , `;
                if(type == 'insert') reslut.lastSql += ` '${data[key]}' ,`
            }else{
                switch(element){
                    case "":
                        if(type == 'update') reslut.firstSql += ` ${key} = '' , `;
                        if(type == 'insert') reslut.lastSql += `''`;
                        break;
                    case 'false':
                        if(type == 'update') reslut.firstSql += ` ${key} = ${false} , `;
                        if(type == 'insert') reslut.lastSql += ` ${false} , `;
                        break;
                    case "true":
                        if(type == 'update') reslut.firstSql += ` ${key} = ${true} , `;
                        if(type == 'insert') reslut.lastSql += ` ${true} , `;
                        break;
                    default :
                        if(type == 'update') reslut.firstSql += ` ${key} = ${element} , `;
                        if(type == 'insert') reslut.lastSql += ` ${element} , `;
                        break
                }
            }    
        }
    }
    if(type == 'update') return reslut.firstSql;
    if(type == 'insert') return reslut;
    return " ";
}


/**
 * 创建sql语句
 * @author zs.duan <zs.duan@qq.com>
 * @param {Function} build 创建sql语句
 * @param {Function} end 创建sql语句
 * @return {object} {sql : sql} 返回对象sql语句
 * */ 
module.exports = new createSql();